package ecom.appcode.dao.daointerface;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import com.sun.xml.internal.fastinfoset.sax.Properties;

import ecom.appcode.common.daobean.UsersDaoBean;
import ecom.appcode.common.utility.DBUltils;

public class UsersDaoImpl implements UsersDao {

    /** check Login email and password */
    public boolean checkLogin(String email, String password) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblUsers WHERE email = ? AND password = ?";
            stm = con.prepareStatement(sql);
            stm.setString(1, email);
            stm.setString(2, password);
            rs = stm.executeQuery();

            if (rs.next()) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /** Register Account */
    public boolean registerAccount(String email, String password, String sex, String birthday, String firstName,
            String lastName, boolean admin, String address, int phoneNumber) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "INSERT INTO tblUsers(email, password, sex, birthday, firstName, lastName, isAdmin, address, phoneNumber) VALUES(?,?,?,?,?,?,?,?,?)";
            stm = con.prepareStatement(sql);

            stm.setString(1, email);
            stm.setString(2, password);
            stm.setString(3, sex);
            stm.setString(4, birthday);
            stm.setString(5, firstName);
            stm.setString(6, lastName);
            stm.setBoolean(7, admin);
            stm.setString(8, address);
            stm.setInt(9, phoneNumber);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /** Select all Users for admin page */
    public List<UsersDaoBean> loadAllUsers() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblUsers";
            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();
            List<UsersDaoBean> list = new ArrayList<UsersDaoBean>();
            while (rs.next()) {
                /** Email */
                String email = rs.getString("email");
                /** Password */
                String password = rs.getString("password");
                /** Sex */
                String sex = rs.getString("sex");
                /** Birthday */
                String birthday = rs.getString("birthday");
                /** First name */
                String firstName = rs.getString("firstName");
                /** Last name */
                String lastName = rs.getString("lastName");
                /** Is admin? */
                boolean admin = rs.getBoolean("isAdmin");
                /** Address */
                String address = rs.getString("address");
                /** Phone number */
                int phoneNumber = rs.getInt("phoneNumber");

                UsersDaoBean user = new UsersDaoBean(email, password, sex, birthday, firstName, lastName, admin,
                        address, phoneNumber);

                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** Select Users for admin page */
    public UsersDaoBean loadUsersByEmail(String strEmail) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DBUltils.makeConnection();
            String sql = "SELECT * FROM tblUsers WHERE email = ?";
            stm = con.prepareStatement(sql);
            stm.setString(1, strEmail);
            rs = stm.executeQuery();
            UsersDaoBean user = new UsersDaoBean();
            while (rs.next()) {
                /** Email */
                String email = rs.getString("email");
                /** Password */
                String password = rs.getString("password");
                /** Sex */
                String sex = rs.getString("sex");
                /** Birthday */
                String birthday = rs.getString("birthday");
                /** First name */
                String firstName = rs.getString("firstName");
                /** Last name */
                String lastName = rs.getString("lastName");
                /** Is admin? */
                boolean admin = rs.getBoolean("isAdmin");
                /** Address */
                String address = rs.getString("address");
                /** Phone number */
                int phoneNumber = rs.getInt("phoneNumber");

                user = new UsersDaoBean(email, password, sex, birthday, firstName, lastName, admin, address,
                        phoneNumber);

            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return null;
    }

    /** update User */
    public boolean updateAccount(String email, String password, String sex, String birthday, String firstName,
            String lastName, boolean admin, String address, int phoneNumber) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = " UPDATE tblUsers SET password = ?, sex = ?, birthday = ?, firstName = ?, lastName = ?, isAdmin = ?, address = ? , phoneNumber = ? WHERE email = ?";
            stm = con.prepareStatement(sql);

            stm.setString(1, password);
            stm.setString(2, sex);
            stm.setString(3, birthday);
            stm.setString(4, firstName);
            stm.setString(5, lastName);
            stm.setBoolean(6, admin);
            stm.setString(7, address);
            stm.setInt(8, phoneNumber);
            stm.setString(9, email);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /** Delete User */
    public boolean deleteAccount(String email) {
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DBUltils.makeConnection();
            String sql = "DELETE FROM tblUsers WHERE email = ?";
            stm = con.prepareStatement(sql);
            stm.setString(1, email);

            int row = stm.executeUpdate();
            if (row > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stm != null) {
                    stm.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

//    /** Send Mail Contact */
//    public void sendEmail(String to, String account, String message) throws AddressException, MessagingException {
//        Properties properties = new Properties();
//        // set properties
//        properties.put("mail.smtp.host", "smtp.gmail.com");
//        properties.put("mail.smtp.socketFactory.port", "465");
//        properties.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
//        properties.put("mail.smtp.auth", "true");
//        properties.put("mail.smtp.port", "465");
//        Session session = Session.getDefaultInstance(properties, new javax.mail.Authenticator() {
//            protected PasswordAuthentication getPasswordAuthentication() {
//                return new PasswordAuthentication("phongbv901@gmail.com", "fuphongbv");
//            }
//        });
//
//        Message mes = new MimeMessage(session);
//        mes.setFrom(new InternetAddress("phongbv901@gmail.com"));
//        mes.setRecipients(Message.RecipientType.TO, InternetAddress.parse(to));
//        // set mail content
//        mes.setSubject("Hello " + account);
//        mes.setText("Your password is: " + message);
//        Transport.send(mes);
//
//    }

}
